﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Data.SqlClient;
using StandardPartCo;
using System.IO;
using System.Configuration;


[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
// 若要允许使用 ASP.NET AJAX 从脚本中调用此 Web 服务，请取消对下行的注释。
// [System.Web.Script.Services.ScriptService]
public class StandardPartService : System.Web.Services.WebService
{
    public StandardPartService()
    {

        //如果使用设计的组件，请取消注释以下行 
        //InitializeComponent(); 
    }

    [WebMethod]
    public String GetConfig()
    {
        var s = new StandardPartlib();
       return s.connstr;
    }

    [WebMethod]
    public void reset()
    {
        var s = new StandardPartlib();
        s.reset();
    }

    [WebMethod]
    public PartType getRootType()
    {
        var s = new StandardPartlib();
        return s.getroottype();
    }

    [WebMethod]
    public PartType[] getSubTypes(int id)
    {
        var s = new StandardPartlib();
        return s.getSubTypes(id);
    }

    [WebMethod]
    public Part[] getTypeParts(int id)
    {
        var s = new StandardPartlib();
        return s.getTypeParts(id);
    }

    [WebMethod]
    public Param[] getTypeParams(int id)
    {
        var s = new StandardPartlib();
        return s.getTypeParams(id);
    }

    //type
    [WebMethod]
    public PartType findType(int id)
    {
        var s = new StandardPartlib();
        return s.findType(id);
    }
    [WebMethod]
    public PartType addType(int parentid, String name)
    {
        PartType p = new PartType();
        p.name = name;
        p.parentid = parentid;

        var s = new StandardPartlib();
        PartType pa = s.addType(p);



        return pa;
    }
    [WebMethod]
    public void deleteType(int id)
    {
        var s = new StandardPartlib();


        s.deleteType(id);

        //删除子分组
        foreach (PartType pt in s.getSubTypes(id))
        {
            s.deleteType(pt.id);
        }
    }
    [WebMethod]
    public void updataType(PartType tp)
    {
        var s = new StandardPartlib();
        s.updataType(tp);
    }

    [WebMethod]
    public void updataTypePicture(PartType tp, byte[] picData)
    {
        var s = new StandardPartlib();
        s.updataTypePicture(tp, picData);
    }

    [WebMethod]
    public byte[] getTypePicture(PartType tp,out String picname )
    {
        var s = new StandardPartlib();
        return s.getTypePicture(tp,out picname);
    }

    //param
    [WebMethod]
    public Param findParam(int id)
    {
        var s = new StandardPartlib();
        return s.findParam(id);
    }

    [WebMethod]
    public Param addParam(Param newparam)
    {
        var s = new StandardPartlib();
        var newp = s.addParam(newparam);


       
        return newp;
    }
    [WebMethod]
    public void deleteParam(int id)
    {
        var s = new StandardPartlib();
        s.deleteParam(id);
    }
    [WebMethod]
    public void updataParam(Param tp)
    {
        var s = new StandardPartlib();
        s.updataParam(tp);
    }

    //part
    [WebMethod]
    public Part findPart(int id)
    {
        var s = new StandardPartlib();
        return s.findPart(id);
    }
    [WebMethod]
    public Part addPart(Part name)
    {
        var s = new StandardPartlib();
        return s.addPart(name);
    }
    [WebMethod]
    public void deletePart(int id)
    {
        var s = new StandardPartlib();
        s.deletePart(id);
    }
    [WebMethod]
    public void updataPart(Part tp)
    {
        var s = new StandardPartlib();
        s.updataPart(tp);
    }
    /*
    [WebMethod]
    public Part[] findBearings(String paramname, String paramvalue)
    {
        var s = new StandardPartlib();
        return s.findBearings(paramname, paramvalue);
    }*/

    //ParamValue
    [WebMethod]
    public void SetParamValue(int partid,int paramid,String val)
    {
        var s = new StandardPartlib();
        s.SetParamValue(partid, paramid, val);
    }
    [WebMethod]
    public ParamValue GetParamValue(int partid, int paramid)
    {
        var s = new StandardPartlib();
        return s.GetParamValue(partid, paramid);
    }
    [WebMethod]
    public void deleteParamValue(int partid, int paramid)
    {
        var s = new StandardPartlib();
        s.deleteParamValue(partid, paramid);
    }
    //ParamGroup
    [WebMethod]
    public ParamGroup findParamGroup(int type_id, int id)
    {
        var s = new StandardPartlib();
        return s.findParamGroup(type_id,id);
    }
    [WebMethod]
    public ParamGroup addParamGroup(ParamGroup name)
    {
        var s = new StandardPartlib();
        return s.addParamGroup(name);
    }
    [WebMethod]
    public void deleteParamGroup(int id)
    {
        var s = new StandardPartlib();
        s.deleteParamGroup(id);
    }
    [WebMethod]
    public void updataParamGroup(ParamGroup tp)
    {
        var s = new StandardPartlib();
        s.updataParamGroup(tp);
    }

    [WebMethod]
    public ParamGroup GetTypeRootParamGroup(int type_id)
    {
        var s = new StandardPartlib();
        return s.GetTypeRootParamGroup(type_id);
    }

    [WebMethod]
    public ParamGroup[] getSubParamGroup(int Group_id)
    {
        var s = new StandardPartlib();
        return s.getSubParamGroup(Group_id);
    }

    [WebMethod]
    public int resetParamGroup(ParamGroup tp)
    {
        var s = new StandardPartlib();
        return s.resetParamGroup(tp);   //返回新的序号
    }
}

public class StandardPartlib 
{
/*
    public static String connstr = "server=127.0.0.1,10000;" + "database=bearing_test;"
+ "Persist Security Info=True;User ID=sa;Password=1qaz2wsx#";
    public static String soure = "127.0.0.1,10000";
*/

    internal String connstr{
        get {
            ConnectionStringSettingsCollection settings = ConfigurationManager.ConnectionStrings;
            ConnectionStringSettings cs = settings["std"];
            return cs.ConnectionString;
        }
    }

    public String name { get; set; }

    public String foo()
    {
       
      
                
        using (SqlConnection con = new SqlConnection(connstr))
        {
            con.Open();

            String cmd = String.Format("SELECT * FROM bearingtype");
            using (SqlCommand command = new SqlCommand(cmd, con))
            {
                SqlDataReader reader = command.ExecuteReader();
                reader.Read();
                String n = Convert.ToString(reader["name"]);
                return n;
            }
        }
    }

    public PartType getroottype()
    {
        using (SqlConnection con = new SqlConnection(connstr))
        {
            con.Open();
            String cmd = String.Format("SELECT * FROM bearingtype where parent = -1");
            using (SqlCommand command = new SqlCommand(cmd, con))
            {
                SqlDataReader reader = command.ExecuteReader();
                reader.Read();
                PartType s = new PartType();
                s.name = Convert.ToString(reader["name"]);
                s.id = Convert.ToInt32(reader["id"]);
                s.parentid = Convert.ToInt32(reader["parent"]);
                s.orderid = Convert.ToInt32(reader["prenode"]);
                s.des = Convert.ToString(reader["des"]);
                return s;
            }
        }
    }

    internal PartType findType(int id)
    {
        using (SqlConnection con = new SqlConnection(connstr))
        {
            con.Open();
            String cmd = String.Format("SELECT * FROM bearingtype where id = {0}",id);
            using (SqlCommand command = new SqlCommand(cmd, con))
            {
                SqlDataReader reader = command.ExecuteReader();
                reader.Read();
                PartType s = new PartType();
                s.name = Convert.ToString(reader["name"]);
                s.id = Convert.ToInt32(reader["id"]);
                s.parentid = Convert.ToInt32(reader["parent"]);
                s.orderid = Convert.ToInt32(reader["prenode"]);
                s.des = Convert.ToString(reader["des"]);
                return s;
            }
        }
    }

    internal PartType[] getSubTypes(int id)
    {
        using (SqlConnection con = new SqlConnection(connstr))
        {
            con.Open();
            String cmd = String.Format("SELECT * FROM bearingtype where parent = {0}", id);
            using (SqlCommand command = new SqlCommand(cmd, con))
            {   
                List<PartType> ls = new List<PartType>();
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {

                    PartType s = new PartType();
                    s.name = Convert.ToString(reader["name"]);
                    s.id = Convert.ToInt32(reader["id"]);
                    s.parentid = Convert.ToInt32(reader["parent"]);
                    s.orderid = Convert.ToInt32(reader["prenode"]);
                    s.des = Convert.ToString(reader["des"]);
                    ls.Add(s);
                }
            
                return ls.ToArray();
            }
        }
    }




    internal Part[] getTypeParts(int id)
    {
        using (SqlConnection con = new SqlConnection(connstr))
        {
            con.Open();
            String cmd = String.Format("SELECT * FROM bearing where typeid = {0}", id);
            using (SqlCommand command = new SqlCommand(cmd, con))
            {
                List<Part> ls = new List<Part>();
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {

                    Part s = new Part();
                    s.name = Convert.ToString(reader["name"]);
                    s.id = Convert.ToInt32(reader["id"]);
                    s.ownertypeid = Convert.ToInt32(reader["typeid"]);
                    s.des = Convert.ToString(reader["des"]);
                    ls.Add(s);
                }
                return ls.ToArray();
            }
        }
    }

    internal Param[] getTypeParams(int id)
    {
        using (SqlConnection con = new SqlConnection(connstr))
        {
            con.Open();
            String cmd = String.Format("SELECT * FROM parameter where owner = {0}", id);
            using (SqlCommand command = new SqlCommand(cmd, con))
            {
                List<Param> ls = new List<Param>();
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {

                    Param s = new Param();
                    s.name = Convert.ToString(reader["name"]);
                    s.id = Convert.ToInt32(reader["id"]);
                    s.ownertypeId = Convert.ToInt32(reader["owner"]);
                    s.des = Convert.ToString(reader["des"]);
                    s.title = Convert.ToString(reader["title"]);
                    s.Unit = Convert.ToString(reader["unit"]);
                    s.type = (StandardPartCo.paramtype)Enum.ToObject(typeof(StandardPartCo.paramtype), Convert.ToInt32(reader["valtype"]));
                    ls.Add(s);
                }
                return ls.ToArray();
            }
        }
    }

    public int getNexttypeid()
    {

        using (SqlConnection con = new SqlConnection(connstr))
        {
            con.Open();
            String cmd =
                "SELECT nextbearingtype FROM nextid;update nextid set nextbearingtype = nextbearingtype+1";
            using (SqlCommand command = new SqlCommand(cmd, con))
            {
                SqlDataReader reader = command.ExecuteReader();
                reader.Read();
                return Convert.ToInt32(reader["nextbearingtype"]);
            }
        }

    }

    internal PartType addType(PartType tp)
    {
        tp.id = getNexttypeid();
        using (SqlConnection con = new SqlConnection(connstr))
        {
            con.Open();
            int newOrder = 0;
            String cmd0 = String.Format("SELECT max(prenode) from bearingtype where parent = {0}", tp.parentid);
            using (SqlCommand command = new SqlCommand(cmd0, con))
            {
                Object o = command.ExecuteScalar();
                if(o != System.DBNull.Value)
                    newOrder = Convert.ToInt32(o);
            }
            String cmd = String.Format("INSERT INTO bearingtype (name,id,parent,prenode,des)VALUES('{0}',{1},{2},{3},'{4}')", tp.name, tp.id, tp.parentid, newOrder, tp.des);
            using (SqlCommand command = new SqlCommand(cmd, con))
            {
                
                command.ExecuteNonQuery();
            }
        }
        //添加默认根分组
        ParamGroup pgnew = new ParamGroup();
        pgnew.type_id = tp.id;
        pgnew.parent = -1;
        pgnew.name = tp.name + "_root";
        pgnew.type = ParamGroupType.group;

        ParamGroup pg = addParamGroup(pgnew);

        return tp;
    }

    internal void deleteType(int id)
    {
        using (SqlConnection con = new SqlConnection(connstr))
        {
            con.Open();
            String cmd = String.Format("DELETE FROM bearingtype WHERE id={0}", id);
            using (SqlCommand command = new SqlCommand(cmd, con))
            {
                
                command.ExecuteNonQuery();
            }
        }

        var grp = GetTypeRootParamGroup(id);
        //删除默认根分组
        deleteParamGroup(grp.id);

        using (SqlConnection con = new SqlConnection(connstr))
        {
            con.Open();
            String cmd = String.Format("DELETE FROM bearing WHERE typeid={0}", id);
            using (SqlCommand command = new SqlCommand(cmd, con))
            {
                
                command.ExecuteNonQuery();
            }
        }
    }

    internal void updataType(PartType tp)
    {
        using (SqlConnection con = new SqlConnection(connstr))
        {
            con.Open();
            String cmd = String.Format("UPDATE bearingtype set name ='{0}',parent ={1},des = '{2}' WHERE id = {3}" , tp.name, tp.parentid, tp.des,tp.id);
            using (SqlCommand command = new SqlCommand(cmd, con))
            {
                
                command.ExecuteNonQuery();
            }
        }
    }

    internal void updataTypePicture(PartType tp, byte[] picData)
    {
        using (SqlConnection con = new SqlConnection(connstr))
        {
            con.Open();
            String cmd = String.Format("UPDATE bearingtype set pictureName ='{0}',pictureData = @UpdateImage WHERE id = {1}", tp.picName, tp.id);
            using (SqlCommand command = new SqlCommand(cmd, con))
            {
                command.Parameters.Add("@UpdateImage", System.Data.SqlDbType.VarBinary);
                command.Parameters["@UpdateImage"].Value = picData;
                command.ExecuteNonQuery();
            }
        }
    }

    internal byte[] getTypePicture(PartType tp,out String picname)
    {
        using (SqlConnection con = new SqlConnection(connstr))
        {
            con.Open();
            String cmd = String.Format("SELECT pictureName,pictureData FROM bearingtype where id = {0}", tp.id);
            using (SqlCommand command = new SqlCommand(cmd, con))
            {
                SqlDataReader reader = command.ExecuteReader();
                reader.Read();

                String pname = Convert.ToString(reader["pictureName"]);
                picname = pname;
               var s = reader["pictureData"];
               if (DBNull.Value == s)
                   return null;
               return (byte[])reader["pictureData"];

            }
        }
    }

    internal Part findPart(int id)
    {
        using (SqlConnection con = new SqlConnection(connstr))
        {
            con.Open();
            String cmd = String.Format("SELECT * FROM bearing where id = {0}", id);
            using (SqlCommand command = new SqlCommand(cmd, con))
            {
                SqlDataReader reader = command.ExecuteReader();
                reader.Read();
                Part s = new Part();
                s.name = Convert.ToString(reader["name"]);
                s.id = Convert.ToInt32(reader["id"]);
                s.ownertypeid = Convert.ToInt32(reader["typeid"]);
                s.des = Convert.ToString(reader["des"]);
                return s;
            }
        }
    }

    internal int getNextbraringid()
    {
        using (SqlConnection con = new SqlConnection(connstr))
        {
            con.Open();
            String cmd =
                 "SELECT nextbearing FROM nextid;update nextid set nextbearing = nextbearing+1";
            using (SqlCommand command = new SqlCommand(cmd, con))
            {
                SqlDataReader reader = command.ExecuteReader();
                reader.Read();
                return Convert.ToInt32(reader["nextbearing"]);
            }
        }
    }

    internal Part addPart(Part pt)
    {
        pt.id = getNextbraringid();
        using (SqlConnection con = new SqlConnection(connstr))
        {
            con.Open();
            String cmd = String.Format("INSERT INTO bearing (name,id,typeid,des)VALUES('{0}',{1},{2},'{3}')", pt.name, pt.id, pt.ownertypeid, pt.des);
            using (SqlCommand command = new SqlCommand(cmd, con))
            {
                
                command.ExecuteNonQuery();
            }
        }
        return pt;
    }

    internal void deletePart(int id)
    {
        using (SqlConnection con = new SqlConnection(connstr))
        {
            con.Open();
            String cmd = String.Format("DELETE FROM bearing WHERE id={0}", id);
            using (SqlCommand command = new SqlCommand(cmd, con))
            {
                
                command.ExecuteNonQuery();
            }
        }
    }

    internal void updataPart(Part tp)
    {
        using (SqlConnection con = new SqlConnection(connstr))
        {
            con.Open();
            String cmd = String.Format("UPDATE bearing set name ='{0}',typeid ={1},des = '{2}' WHERE id = {3}", tp.name, tp.ownertypeid, tp.des,tp.id);
            using (SqlCommand command = new SqlCommand(cmd, con))
            {
                
                command.ExecuteNonQuery();
            }
        }
    }

    internal Param findParam( int id)
    {
        using (SqlConnection con = new SqlConnection(connstr))
        {
            con.Open();
            String cmd = String.Format("SELECT * FROM parameter where id = {0}", id);
            using (SqlCommand command = new SqlCommand(cmd, con))
            {
                SqlDataReader reader = command.ExecuteReader();
                reader.Read();
                Param s = new Param();
                s.name = Convert.ToString(reader["name"]);
                s.id = Convert.ToInt32(reader["id"]);
                s.ownertypeId = Convert.ToInt32(reader["owner"]);
                s.des = Convert.ToString(reader["des"]);
                s.Unit = Convert.ToString(reader["unit"]);
                s.title = Convert.ToString(reader["title"]);
                s.type = (StandardPartCo.paramtype)Enum.ToObject(typeof(StandardPartCo.paramtype), Convert.ToInt32(reader["valtype"]));

                return s;
            }
        }
    }

    int getNextparamid()
    {
        using (SqlConnection con = new SqlConnection(connstr))
        {
            con.Open();
            String cmd =
                 "SELECT nextparam FROM nextid;update nextid set nextparam = nextparam+1";
            using (SqlCommand command = new SqlCommand(cmd, con))
            {
                SqlDataReader reader = command.ExecuteReader();
                reader.Read();
                return Convert.ToInt32(reader["nextparam"]);
            }
        }
    }

    internal Param addParam(Param newparam)
    {
        newparam.id = getNextparamid();
        using (SqlConnection con = new SqlConnection(connstr))
        {
            con.Open();
            String cmd = String.Format(
                "INSERT INTO parameter (name,id,owner,unit,title,des,valtype)VALUES('{0}',{1},{2},'{3}','{4}','{5}',{6})",
                newparam.name, newparam.id, newparam.ownertypeId, newparam.Unit, newparam.title, newparam.des, (int)newparam.type);
            using (SqlCommand command = new SqlCommand(cmd, con))
            {
                
                command.ExecuteNonQuery();
            }
        }
        //添加组数据
        var grp = GetTypeRootParamGroup(newparam.ownertypeId);
        ParamGroup pg = new ParamGroup();
        pg.parent = grp.id;
        pg.type_id = newparam.ownertypeId;
        pg.type = ParamGroupType.leaf;
        pg.param = newparam.id;
        addParamGroup(pg);
        return newparam;
    }

    internal void deleteParam(int id)
    {
        using (SqlConnection con = new SqlConnection(connstr))
        {
            con.Open();
            String cmd = String.Format("DELETE FROM parameter WHERE id={0}", id);
            using (SqlCommand command = new SqlCommand(cmd, con))
            {
                command.ExecuteNonQuery();
            }
        
                //删除组数据
            String cmd0 = String.Format("DELETE FROM paramGroup WHERE paramleaf={0}", id);
            using (SqlCommand command = new SqlCommand(cmd0, con))
            {
                command.ExecuteNonQuery();
            }
            //处理子组
        }
    }

    internal void updataParam(Param tp)
    {
        using (SqlConnection con = new SqlConnection(connstr))
        {
            con.Open();
            String cmd = String.Format("UPDATE parameter set name ='{0}',valtype = {1},des = '{2}',unit= '{3}',title= '{4}' WHERE id = {5}", tp.name, (int)tp.type, tp.des, tp.Unit, tp.title, tp.id);
            using (SqlCommand command = new SqlCommand(cmd, con))
            {
                
                command.ExecuteNonQuery();
            }
        }
    }

    internal void SetParamValue(int partid, int paramid, String val)
    {
        Object data = null;
        using (SqlConnection con = new SqlConnection(connstr))
        {
            con.Open();
            String cmd1 = String.Format("SELECT valuedata FROM parameterValue where bearingid = {0} AND paramid = {1}", partid, paramid);
            using (SqlCommand command = new SqlCommand(cmd1, con))
            {
                data = command.ExecuteScalar();
            }

            String cmd = null;
            if (data == null)
                cmd = String.Format("INSERT INTO parameterValue (bearingid,paramid,valuedata)VALUES({0},{1},'{2}')", partid, paramid, val);
            else
                cmd = String.Format("UPDATE parameterValue set valuedata ='{0}' where bearingid = {1} AND paramid = {2}", val,partid, paramid);
           
            using (SqlCommand command = new SqlCommand(cmd, con))
            {
                command.ExecuteNonQuery();
            }
        }
    }

    internal ParamValue GetParamValue(int partid, int paramid)
    {
        using (SqlConnection con = new SqlConnection(connstr))
        {
            con.Open();
            String cmd = String.Format("SELECT valuedata FROM parameterValue where bearingid = {0} AND paramid = {1}", partid, paramid);
            using (SqlCommand command = new SqlCommand(cmd, con))
            {
                Object data = command.ExecuteScalar();
                if (data != null)
                {
                    var s = new ParamValue();
                    s.partid = partid;
                    s.paramid = paramid;
                    s.value = Convert.ToString(data);
                    return s;
                }
            }
        }
        return null;
    }

    internal void deleteParamValue(int partid, int paramid)
    {
        using (SqlConnection con = new SqlConnection(connstr))
        {
            con.Open();
            String cmd = String.Format("DELETE FROM parameterValue where bearingid = {0} AND paramid = {1}", partid, paramid);
            using (SqlCommand command = new SqlCommand(cmd, con))
            {
                command.ExecuteNonQuery();
            }
        }
    }

    internal ParamGroup findParamGroup(int type_id, int id)
    {
        using (SqlConnection con = new SqlConnection(connstr))
        {
            con.Open();
            String cmd = String.Format("SELECT * FROM paramGroup where id = {0} AND typeid = {1}", id, type_id);
            using (SqlCommand command = new SqlCommand(cmd, con))
            {
                SqlDataReader reader = command.ExecuteReader();
                reader.Read();
                ParamGroup s = new ParamGroup();
                s.name = Convert.ToString(reader["name"]);
                s.id = Convert.ToInt32(reader["id"]);
                s.type_id = Convert.ToInt32(reader["typeid"]);
                s.parent = Convert.ToInt32(reader["parentgrp"]);
                s.index = Convert.ToInt32(reader["index__"]);
                s.param = Convert.ToInt32(reader["paramleaf"]);
                s.type = (StandardPartCo.ParamGroupType)Enum.ToObject(typeof(StandardPartCo.ParamGroupType), Convert.ToInt32(reader["type"]));

                return s;
            }
        }
    }

    int getNextgroupid()
    {
        using (SqlConnection con = new SqlConnection(connstr))
        {
            con.Open();
            String cmd =
                 "SELECT nextgroup FROM nextid;update nextid set nextgroup = nextgroup+1";
            using (SqlCommand command = new SqlCommand(cmd, con))
            {
                SqlDataReader reader = command.ExecuteReader();
                reader.Read();
                return Convert.ToInt32(reader["nextgroup"]);
            }
        }
    }

    internal ParamGroup addParamGroup(ParamGroup newparam)
    {
        newparam.id = getNextgroupid();
        using (SqlConnection con = new SqlConnection(connstr))
        {
            con.Open();

            int newOrder = 10;
            String cmd0 = String.Format(
               "SELECT max(index__) from paramGroup where  typeid = {0} AND parentgrp = {1}",
                     newparam.type_id, newparam.parent);
            using (SqlCommand command = new SqlCommand(cmd0, con))
            {
                Object o = command.ExecuteScalar();
                if (o != System.DBNull.Value)
                {
                    newOrder = Convert.ToInt32(o);
                    newOrder += 10;
                }
            }
            newparam.index = newOrder;
            String cmd = String.Format(
                "INSERT INTO paramGroup (name,id,typeid,parentgrp,index__,paramleaf,type)VALUES('{0}',{1},{2},{3},{4},{5},{6})",
                newparam.name, newparam.id, newparam.type_id, newparam.parent, newparam.index, newparam.param,(int)newparam.type);
            using (SqlCommand command = new SqlCommand(cmd, con))
            {
                
                command.ExecuteNonQuery();
            }
        }
        return newparam;
    }

    internal void deleteParamGroup(int id)
    {
        ParamGroup s = new ParamGroup();
        using (SqlConnection con = new SqlConnection(connstr))
        {
            con.Open();
            String cmd = String.Format("SELECT * FROM paramGroup where id = {0}", id);
            using (SqlCommand command = new SqlCommand(cmd, con))
            {
                SqlDataReader reader = command.ExecuteReader();
                reader.Read();
                s.name = Convert.ToString(reader["name"]);
                s.id = Convert.ToInt32(reader["id"]);
                s.type_id = Convert.ToInt32(reader["typeid"]);
                s.parent = Convert.ToInt32(reader["parentgrp"]);
                s.index = Convert.ToInt32(reader["index__"]);
                s.param = Convert.ToInt32(reader["paramleaf"]);
                s.type = (StandardPartCo.ParamGroupType)Enum.ToObject(typeof(StandardPartCo.ParamGroupType), Convert.ToInt32(reader["type"]));
            }
        }

        var rootGroup = GetTypeRootParamGroup(s.type_id);
        if (s.type == ParamGroupType.leaf)
        {
            s.parent = rootGroup.id;
            updataParamGroup(s);
        }
        else
        {
                    //处理子组
            foreach (var cgrp in getSubParamGroup(id))
            {
                deleteParamGroup(cgrp.id);
            }

            using (SqlConnection con = new SqlConnection(connstr))
            {
                con.Open();
                String cmd = String.Format("DELETE FROM paramGroup WHERE id={0}", id);
                using (SqlCommand command = new SqlCommand(cmd, con))
                {
                    command.ExecuteNonQuery();
                }
                
            }
        }
        

    }

    internal void updataParamGroup(ParamGroup tp)
    {
        using (SqlConnection con = new SqlConnection(connstr))
        {
            con.Open();
            String cmd = String.Format
                ("UPDATE paramGroup set name ='{0}',parentgrp = {1},index__ = {2} WHERE id = {3}",
                tp.name, (int)tp.parent, tp.index, tp.id);
            using (SqlCommand command = new SqlCommand(cmd, con))
            {
                
                command.ExecuteNonQuery();
            }
        }
    }

    internal ParamGroup GetTypeRootParamGroup(int type_id)
    {
        ParamGroup result = null;
        using (SqlConnection con = new SqlConnection(connstr))
        {
            con.Open();
            String cmd = String.Format("SELECT * FROM paramGroup where typeid = {0} AND parentgrp = -1", type_id);
            using (SqlCommand command = new SqlCommand(cmd, con))
            {
                SqlDataReader reader = command.ExecuteReader();
                if (reader.Read())
                {


                    ParamGroup s = new ParamGroup();
                    s.name = Convert.ToString(reader["name"]);
                    s.id = Convert.ToInt32(reader["id"]);
                    s.type_id = Convert.ToInt32(reader["typeid"]);
                    s.parent = Convert.ToInt32(reader["parentgrp"]);
                    s.index = Convert.ToInt32(reader["index__"]);
 
                    s.type = (StandardPartCo.ParamGroupType)Enum.ToObject(typeof(StandardPartCo.ParamGroupType), Convert.ToInt32(reader["type"]));
                    if (s.type == ParamGroupType.leaf)
                    {
                        s.param = Convert.ToInt32(reader["paramleaf"]);
                    }
                    result = s;
                }
                else
                    result =  null;
            }
        }

        //checkParamGroup(type_id, result);
        return result;
    }

    private void checkParamGroup(int type_id, int root_grpid)
    {
        //检查没有分组的Param
        using (SqlConnection con = new SqlConnection(connstr))
        {
            List<int> ids = new List<int>();
            con.Open();
            String cmd1 = String.Format("SELECT * FROM parameter where owner = {0}", type_id);
            using (SqlCommand command1 = new SqlCommand(cmd1, con))
            {
                SqlDataReader reader1 = command1.ExecuteReader();
                while (reader1.Read())
                {

                    int param_id = Convert.ToInt32(reader1["id"]);
                    ids.Add(param_id);
                }
                reader1.Close();
            }

            foreach (int ss in ids)
            {
                String cmd2 = String.Format("SELECT * FROM paramGroup where typeid = {0} AND type = 0 AND paramleaf = {1}", type_id, ss);
                using (SqlCommand command2 = new SqlCommand(cmd2, con))
                {
                    SqlDataReader reader2 = command2.ExecuteReader();
                    if (reader2.HasRows == false)
                    {
                        ParamGroup pg = new ParamGroup();
                        pg.parent = root_grpid;
                        pg.type_id = type_id;
                        pg.type = ParamGroupType.leaf;
                        pg.param = ss;
                        addParamGroup(pg);
                    }
                    reader2.Close();
                }

            }
        }
    }

    internal ParamGroup[] getSubParamGroup(int Group_id)
    {
        using (SqlConnection con = new SqlConnection(connstr))
        {
            con.Open();
            String cmd = String.Format("SELECT * FROM paramGroup where parentgrp = {0}", Group_id);
            using (SqlCommand command = new SqlCommand(cmd, con))
            {
                SqlDataReader reader = command.ExecuteReader();
                List<ParamGroup> ls = new List<ParamGroup>();
                while (reader.Read())
                {
                    ParamGroup s = new ParamGroup();
                    s.name = Convert.ToString(reader["name"]);
                    s.id = Convert.ToInt32(reader["id"]);
                    s.type_id = Convert.ToInt32(reader["typeid"]);
                    s.parent = Convert.ToInt32(reader["parentgrp"]);
                    s.index = Convert.ToInt32(reader["index__"]);
                    s.param = Convert.ToInt32(reader["paramleaf"]);
                    s.type = (StandardPartCo.ParamGroupType)Enum.ToObject(typeof(StandardPartCo.ParamGroupType), Convert.ToInt32(reader["type"]));
                    ls.Add(s);
                }

                return ls.ToArray();
            }
        }
    }

    internal int resetParamGroup(ParamGroup tp)
    {

       

        using (SqlConnection con = new SqlConnection(connstr))
        {
            con.Open();

            int rootgrp = 0;
            String cmd0 = String.Format("SELECT id FROM paramGroup where typeid = {0} AND parentgrp = -1", tp.type_id);
            using (SqlCommand command = new SqlCommand(cmd0, con))
            {
                rootgrp = (int)command.ExecuteScalar();
            }

            int newOrder = 10;
            String cmd1 = String.Format(
               "SELECT max(index__) from paramGroup where  typeid = {0} AND parentgrp = {1}",
                      tp.type_id, rootgrp);
            using (SqlCommand command = new SqlCommand(cmd1, con))
            {
                Object o = command.ExecuteScalar();
                if (o != System.DBNull.Value)
                {
                    newOrder = Convert.ToInt32(o);
                    newOrder += 10;
                }
            }
            tp.index = newOrder;
            tp.parent = rootgrp;
            String cmd = String.Format
                ("UPDATE paramGroup set name ='{0}',parentgrp = {1},index__ = {2} WHERE id = {3}",
                tp.name, (int)tp.parent, tp.index, tp.id);
            using (SqlCommand command = new SqlCommand(cmd, con))
            {
                command.ExecuteNonQuery();
            }
            return newOrder;
        }
    }


    public void reset()
    {
        using (SqlConnection con = new SqlConnection(connstr))
        {
            con.Open();
            try
            {
                using (SqlCommand command = new SqlCommand(ult.deletetable, con))
                {
                    command.ExecuteNonQuery();
                }

            }
            catch
            {
                Console.WriteLine("Table not created.");
            }

            try
            {
                using (SqlCommand command = new SqlCommand(ult.buildingtable, con))
                {
                    command.ExecuteNonQuery();
                }
            }
            catch
            {
                Console.WriteLine("Table not created.");
            }
        }
    }

 










    public class ult
    {
        public static String buildingtable =
@"

CREATE TABLE [nextid](
	[nextbearingtype] [int] NOT NULL,
	[nextbearing] [int] NOT NULL,
	[nextparam] [int] NOT NULL,
	[nextgroup] [int] NOT NULL
) ON [PRIMARY]


INSERT INTO [nextid]
           ([nextbearingtype]
           ,[nextbearing]
           ,[nextparam]
           ,[nextgroup])
     VALUES
           (1
           ,1
           ,1
           ,1)


CREATE TABLE bearingtype(
kid INT IDENTITY(1,1) NOT NULL,
id int NOT NULL,
name nvarchar(50) NOT NULL,
parent int NOT NULL,
prenode int NOT NULL,
des nvarchar(max) NULL,
pictureName nvarchar(50) NULL,
pictureData varbinary(max) NULL)


INSERT INTO bearingtype
           ([id]
           ,[name]
           ,[parent]
           ,[prenode]
            )
     VALUES
           (0
           ,'轴承库'
           ,-1
           ,1
            )

CREATE TABLE [bearing](
	[kid] [int] IDENTITY(1,1) NOT NULL,
	[id] [int] NOT NULL,
	[name] [nvarchar](50) NOT NULL,
	[typeid] [int] NOT NULL,
	[des] [nvarchar](max) NULL)

CREATE TABLE [parameter](
	[kid] [int] IDENTITY(1,1) NOT NULL,
	[id] [int] NOT NULL,
	[owner] [int] NOT NULL,
	[name] [nvarchar](50) NOT NULL,
	[title] [nvarchar](50) NULL,
	[valtype] [int] NOT NULL,
	[unit] [nvarchar](50) NULL,
	[des] [nvarchar](max) NULL)

CREATE TABLE [parameterValue](
	[kid] [int] IDENTITY(1,1) NOT NULL,
	[bearingid] [int] NOT NULL,
	[paramid] [int] NOT NULL,
	[valuedata] [nvarchar](max) NULL)

CREATE TABLE [paramGroup](
	[kid] [int] IDENTITY(1,1) NOT NULL,
	[id] [int] NOT NULL,
	[name] [nvarchar](50) NOT NULL,
	[typeid] [int] NOT NULL,
	[parentgrp] [int] NOT NULL,
	[type] [int] NOT NULL,
	[paramleaf] [int] NULL,
	[index__] [int] NOT NULL)

INSERT INTO paramGroup (name,id,typeid,parentgrp,index__,type)
VALUES('Root',0,0,-1,0,1)

";

        public static String deletetable =
@"
DROP TABLE nextid
DROP TABLE bearingtype
DROP TABLE bearing
DROP TABLE parameter
DROP TABLE parameterValue
DROP TABLE paramGroup
";


    }
}





